﻿// -----------------------------------------------------------------------
// <copyright file="DepartmentData.cs" company="VDC-IT" author="LTDUC">
//      Copyright (c) VDC-IT. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace VDC.VONE.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;
    using Microsoft.ApplicationBlocks.Data;
    using VDC.COMMON;
    using VDC.VONE.ENTITIES;
    
    /// <summary>
    /// Class access to db Department
    /// </summary>
    public class DepartmentData
    {
        /// <summary>
        /// Get Department By Id
        /// </summary>
        /// <param name="departmentId">Param departmentId</param>
        /// <returns>Return Department</returns>
        public DepartmentEN GetDepartmentById(int departmentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                DepartmentEN department = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentGetById, departmentId);
                while (reader.Read())
                {
                    department = new DepartmentEN();

                    if (!reader.IsDBNull(0))
                    {
                        department.DepartmentId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        department.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        department.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        department.Parent = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        department.Order = reader.GetInt32(4);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return department;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the phong ban con by id.
        /// </summary>
        /// <param name="departmentId">The department id.</param>
        /// <exception cref="Exception">Exception Ex</exception>
        /// <returns>Return DepartmentList</returns>
        public IList<DepartmentEN> GetPhongBanConById(int departmentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<DepartmentEN> lstDepartment = new List<DepartmentEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentGetPhongBanConById, departmentId);
                while (reader.Read())
                {
                    DepartmentEN department = new DepartmentEN();

                    if (!reader.IsDBNull(0))
                    {
                        department.DepartmentId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        department.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        department.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        department.Parent = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        department.Order = reader.GetInt32(4);
                    }

                    lstDepartment.Add(department);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstDepartment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Get list all Department
        /// </summary>
        /// <returns>Return list all Department</returns>
        public IList<DepartmentEN> GetDepartments()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<DepartmentEN> lstDepartment = new List<DepartmentEN>();
                SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentGetAll);
                while (reader.Read())
                {
                    DepartmentEN department = new DepartmentEN();

                    if (!reader.IsDBNull(0))
                    {
                        department.DepartmentId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        department.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        department.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        department.Parent = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        department.Order = reader.GetInt32(4);
                    }

                    lstDepartment.Add(department);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstDepartment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Check Department By Id
        /// </summary>
        /// <param name="departmentId">Param departmentId</param>
        /// <returns>Return Department</returns>
        public DepartmentEN CheckDepartmentById(int departmentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                DepartmentEN department = null;
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentCheck, departmentId);
                while (reader.Read())
                {
                    department = new DepartmentEN();

                    if (!reader.IsDBNull(0))
                    {
                        department.DepartmentId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        department.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        department.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        department.Parent = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        department.Order = reader.GetInt32(4);
                    }
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return department;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Insert to db Department
        /// </summary>
        /// <param name="department">Paarm Department</param>
        /// <returns>Insert Department</returns>
        public int Insert(DepartmentEN department)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Department.Code, department.Code),
                                    new SqlParameter(Constants.SqlParamerterName.Department.Name, department.Name),
                                    new SqlParameter(Constants.SqlParamerterName.Department.Parent, department.Parent),
                                    new SqlParameter(Constants.SqlParamerterName.Department.Order, department.Order)
                                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentInsert, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Update to db Department
        /// </summary>
        /// <param name="department">Param Department</param>
        public void Update(DepartmentEN department)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[]
                                {
                                    new SqlParameter(Constants.SqlParamerterName.Department.DepartmentId, department.DepartmentId),
                                    new SqlParameter(Constants.SqlParamerterName.Department.Code, department.Code),
                                    new SqlParameter(Constants.SqlParamerterName.Department.Name, department.Name),
                                    new SqlParameter(Constants.SqlParamerterName.Department.Parent, department.Parent),
                                    new SqlParameter(Constants.SqlParamerterName.Department.Order, department.Order)
                                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentUpdate, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Delete Department by departmentId
        /// </summary>
        /// <param name="departmentId">param departmentId</param>
        public void Delete(int departmentId)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.Department.DepartmentId, departmentId)
                };

                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentDelete, sqlParameters);
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Exist code
        /// </summary>
        /// <param name="departmentId">Param departmentId</param>
        /// <param name="code">Param code</param>
        /// <returns>Return Exist</returns>
        public bool Exist(int departmentId, string code)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                var sqlParameters = new[] 
                { 
                    new SqlParameter(Constants.SqlParamerterName.Department.DepartmentId, departmentId),
                    new SqlParameter(Constants.SqlParamerterName.Department.Code, code)
                };

                var result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentExist, sqlParameters));
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return result > 0;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Gets the department by parent id.
        /// </summary>
        /// <param name="parentId">The parent id.</param>
        /// <param name="level">The level.</param>
        /// <returns>return lstDepartment</returns>
        /// <exception cref="System.Exception">SqlException ex</exception>
        public IList<DepartmentEN> GetDepartmentByParentId(int parentId, int level)
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<DepartmentEN> lstDepartment = new List<DepartmentEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentByParentId, parentId, level);
                while (reader.Read())
                {
                    var department = new DepartmentEN();

                    if (!reader.IsDBNull(0))
                    {
                        department.DepartmentId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        department.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        department.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        department.Parent = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        department.Order = reader.GetInt32(4);
                    }

                    lstDepartment.Add(department);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstDepartment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }

        public IList<DepartmentEN> GetDepartmentWorkflowProfile()
        {
            string methodName = MethodBase.GetCurrentMethod().DeclaringType.ToString() + Constants.Dot + MethodBase.GetCurrentMethod().Name;
            LogHelper.Instance.WriteInfo(Constants.Begin + methodName);
            try
            {
                IList<DepartmentEN> lstDepartment = new List<DepartmentEN>();
                var reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, Constants.StorepProcedure.VOneDepartmentWorkflowProfile);
                while (reader.Read())
                {
                    var department = new DepartmentEN();

                    if (!reader.IsDBNull(0))
                    {
                        department.DepartmentId = reader.GetInt32(0);
                    }

                    if (!reader.IsDBNull(1))
                    {
                        department.Code = reader.GetString(1);
                    }

                    if (!reader.IsDBNull(2))
                    {
                        department.Name = reader.GetString(2);
                    }

                    if (!reader.IsDBNull(3))
                    {
                        department.Parent = reader.GetInt32(3);
                    }

                    if (!reader.IsDBNull(4))
                    {
                        department.Order = reader.GetInt32(4);
                    }

                    lstDepartment.Add(department);
                }

                reader.Close();
                reader.Dispose();
                LogHelper.Instance.WriteInfo(Constants.End + methodName);
                return lstDepartment;
            }
            catch (SqlException ex)
            {
                LogHelper.Instance.WriteError(methodName + Constants.Colon + ex.Message);
                throw new Exception(ex.ToString());
            }
        }
    }
}